package com.gd.iot.energy.util;

import com.gd.iot.energy.annotation.ExcelFieldOrder;
import com.gd.iot.energy.code.ResponseCode;
import com.gd.iot.energy.exception.MyException;
import com.gd.iot.energy.pojo.common.vo.ColumnVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.StringUtils;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.*;
import java.util.stream.Collectors;


/**
 * @author kris
 * @ClassName: ExcelOperUtil.java
 * @Description: 读取excel内数据，返回封装对象，支持多excel Sheet，支持指定行开始读取
 */
@Slf4j
public class CustomExcelOperateUtil {
    private static final String NUMERIC_TYPE = "NUMERIC";
    private static final String STRING_TYPE = "STRING";
    private static final String BLANK_TYPE = "BLANK";
    private static final String ERROR_TYPE = "ERROR";

    private static final String TYPE_JAVA_BIG_DECIMAL = "BigDecimal";
    private static final String TYPE_JAVA_DATE = "Date";
    private static final String TYPE_JAVA_STRING = "String";
    private static final String TYPE_JAVA_INTEGER = "Integer";
    private static final String TYPE_JAVA_DOUBLE = "Double";
    private static final String TYPE_JAVA_LONG = "Long";

    private static final String EXCEL2003L = ".xls";
    private static final String EXCEL2007U = ".xlsx";
    private static final String EXCELCSV = ".csv";


    /**
     * @param fileName       带后缀文件名
     * @param modelclasslist 需要返回封装对象类型集合
     * @param startRows      指定行开始读取
     * @param sheetNames     sheet名称校验
     * @return
     * @throws Exception
     * @Title: getBankListByExcel
     * @Description: 读取excel内数据，返回封装对象，支持多excel，支持指定行开始读取
     * @return: List<List               <               Object>> 多sheet 数据封装对象
     */
    public List<List<Object>> getBankListByExcel(File file, String fileName, List<String> sheetNames, List<Class> modelclasslist, List<Integer> startRows) throws Exception {
        if (null == file) {
            return new ArrayList<>();
        }

        List<List<Object>> list = null;
        try (InputStream in = new FileInputStream(file)) {

            // 创建Excel工作薄
            Workbook work = this.getWorkbook(in, fileName);
            //检查excel
            checkWork(modelclasslist, work, startRows, sheetNames);

            Sheet sheet = null;


            list = new ArrayList<>();

            // 遍历Excel中所有的sheet
            for (int i = 0; i < work.getNumberOfSheets(); i++) {

                Class modelClass = modelclasslist.get(i);

                Map<Integer, Field> fieldMap = getFieldMap(modelClass);

                sheet = work.getSheetAt(i);
                if (sheet == null) {
                    continue;
                }

                checkSheet(sheet.getSheetName(), sheetNames.get(i));

                List<Object> sheetList = generateSheetList(startRows.get(i), sheet, modelClass, fieldMap);

                list.add(sheetList);
            }
            return list;
        } catch (MyException e) {
            throw e;
        } catch (Exception e) {
            log.error("获取解析excel数据异常:"+e.getMessage(), e);
            throw new MyException(ResponseCode.PARSE_EXCEL_FAILED);
        }
    }

    /**
     * 生成sheet中的数据
     *
     * @param startRow   开始行
     * @param sheet      sheet
     * @param modelClass 模型
     * @param fieldMap   字段map
     * @return 数据List
     * @throws Exception
     */
    private List<Object> generateSheetList(int startRow, Sheet sheet, Class modelClass, Map<Integer, Field> fieldMap) throws Exception {
        List<Object> sheetList = new ArrayList<>(sheet.getLastRowNum());
        // 遍历当前sheet中的所有行
        //定义开始行num
        Row row = null;
        Cell cell = null;
        for (int j = startRow; j <= sheet.getLastRowNum(); j++) {

            Object obj = modelClass.newInstance();

            row = sheet.getRow(j);
            if (null == row) {
                continue;
            }

            // 遍历所有的列
            for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {

                cell = row.getCell(y);
                Field field = fieldMap.get(y);
                if (null == field) {
                    continue;
                }

                constructionObj(modelClass, field, obj, getCellValue(cell));

            }
            sheetList.add(obj);
        }
        return sheetList;
    }

    /**
     * 检查excel
     *
     * @param modelclasslist 模型列表
     * @param work           工作表
     * @param startRows      开始行
     * @param sheetNames     sheet 名称列表
     */
    private void checkWork(List<Class> modelclasslist, Workbook work, List<Integer> startRows, List<String> sheetNames) {
        if (null == work) {
            throw new MyException("创建Excel工作薄为空！");
        }

        if (null == modelclasslist || modelclasslist.size() != work.getNumberOfSheets()) {
            throw new MyException("ModelClass 参数个数和excel Sheet数量不一致");
        }

        if (null == startRows || startRows.size() != work.getNumberOfSheets()) {
            throw new MyException("起始行参数个数和excel Sheet数量不一致");
        }

        if (!sheetNames.isEmpty() && sheetNames.size() != work.getNumberOfSheets()) {
            throw new MyException("待校验的sheet名称和excel Sheet数量不一致");
        }
    }

    /**
     * 检查sheet
     *
     * @param sheetName       指定的sheet
     * @param sheetNameImport 上传的sheet
     */
    private void checkSheet(String sheetName, String sheetNameImport) {
        if (!sheetNameImport.equals(sheetName)) {
            throw new MyException("sheet名称不正确");
        }
    }

    /**
     * @param clazz
     * @param instance
     * @param args
     * @throws SecurityException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     * @Title: constructionObj
     * @Description: 为实例化对象某个属性赋值
     * @return: void
     */
    private void constructionObj(Class clazz, Field field, Object instance, String args) throws Exception {
        String fieldName = field.getName();
        String type = field.getType().getSimpleName();
        String methodName = "set" + Character.toUpperCase(fieldName.charAt(0)) + fieldName.substring(1);
        Method method = clazz.getMethod(methodName, field.getType());

        if (null == args) {
            return;
        }

        switch (type) {
            case TYPE_JAVA_DATE:
                method.invoke(instance, CustomDateUtil.parseExcelDate(args));
                break;
            case TYPE_JAVA_BIG_DECIMAL:
                method.invoke(instance, getBigDecimal(args));
                break;
            case TYPE_JAVA_STRING:
                method.invoke(instance, args);
                break;
            case TYPE_JAVA_INTEGER:
                if (StringUtils.isEmpty(args)) {
                    method.invoke(instance, (Object) null);
                } else {
                    method.invoke(instance, (int) (Double.parseDouble(args)));
                }
                break;
            case TYPE_JAVA_DOUBLE:
                if (StringUtils.isEmpty(args)) {
                    method.invoke(instance, (Object) null);
                } else {
                    method.invoke(instance, Double.parseDouble(args));
                }
                break;
            case TYPE_JAVA_LONG:
                if (StringUtils.isEmpty(args)) {
                    method.invoke(instance, (Object) null);
                } else {
                    method.invoke(instance, (long) (Double.parseDouble(args)));
                }
                break;
            default:
                method.invoke(instance, args);
        }
    }

    /**
     * @param value
     * @return
     * @Title: getBigDecimal
     * @Description: Object -> BigDecimal
     * @return: BigDecimal
     */
    private static BigDecimal getBigDecimal(Object value) {
        try {
            BigDecimal ret = null;
            if (value != null) {
                if (value instanceof BigDecimal) {
                    ret = (BigDecimal) value;
                } else if (value instanceof String && StringUtils.hasText((String) value)) {
                    ret = BigDecimal.valueOf(Double.parseDouble(value.toString()));
                } else if (value instanceof BigInteger) {
                    ret = BigDecimal.valueOf(Double.parseDouble(String.valueOf(value)));
                } else if (value instanceof Number) {
                    ret = BigDecimal.valueOf(((Number) value).doubleValue());
                } else {
                    return null;
                }
            }
            return ret;
        } catch (Exception e) {
            log.error("转换BigDecimal异常,value:", value);
            throw new MyException(ResponseCode.PARAMETER_ERROR);
        }
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     *
     * @param inStr ,fileName
     * @return
     * @throws Exception
     */
    private Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf('.'));
        if (EXCEL2003L.equals(fileType)) {
            // 2003-
            wb = new HSSFWorkbook(inStr);
        } else if (EXCEL2007U.equals(fileType)) {
            // 2007+
            wb = new XSSFWorkbook(inStr);
        } else {
            throw new MyException(ResponseCode.PARAMETER_ERROR);
        }
        return wb;
    }

    /**
     * 描述：对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
    private String getCellValue(Cell cell) {
        if (null == cell) {
            return null;
        }
        if (NUMERIC_TYPE.equals(cell.getCellType().name())) {
            return String.valueOf(cell.getNumericCellValue());
        } else if (STRING_TYPE.equals(cell.getCellType().name())) {
            return cell.getStringCellValue();
        } else if (BLANK_TYPE.equals(cell.getCellType().name())) {
            return null;
        }else if (ERROR_TYPE.equals(cell.getCellType().name())){
            log.error(cell.toString());
            return null;
        }
        cell.setCellFormula(cell.getCellFormula());
        return cell.getStringCellValue();
    }

    /**
     * 根据模型获取字段和排序
     *
     * @param modelClass
     * @return
     */
    private Map<Integer, Field> getFieldMap(Class modelClass) {

        Field[] fields = modelClass.getDeclaredFields();
        Map<Integer, Field> fieldMap = new HashMap<>(fields.length);

        for (Field field : fields) {
            ExcelFieldOrder excelFieldOrder = field.getAnnotation(ExcelFieldOrder.class);
            if (null != excelFieldOrder) {
                fieldMap.put(excelFieldOrder.order(), field);
            }

        }
        return fieldMap;
    }

    /**
     * dataList中的元素为javaBean类型导出
     * @param filePath
     * @param fileName
     * @param columns
     * @param dataList
     */
    public static String exportExcel(String filePath, String fileName, List<ColumnVO> columns, List dataList) {
        // 声明一个工作薄
        XSSFWorkbook wb = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = wb.createSheet("sheet1");
        //创建标题行
        XSSFRow row = sheet.createRow(0);
        //单元格格式
        XSSFCellStyle style = wb.createCellStyle();
        Font boldFont = wb.createFont();
        boldFont.setBold(true);
        style.setFont(boldFont);

//        创建excel表头
//        XSSFCell cell = null;
//        for (int i = 0; i < columns.size(); i++) {
//            cell = row.createCell(i);
//            cell.setCellType(CellType.STRING);
//            String unit = columns.get(i).getUnit();
//
//            if (StringUtils.hasText(unit)) {
//                cell.setCellValue(columns.get(i).getName() + "(" + unit + ")");
//            } else {
//                cell.setCellValue(columns.get(i).getName());
//            }
//        }

        int rowNum = 0;
        if (needMerge(columns)) {
            XSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);

            List<CellRangeAddress> mergeHeadRegion = getMergeHeadRegion(columns);
            mergeHeadRegion.forEach(sheet::addMergedRegion);
            rowNum = setMergedColumnHead(sheet, rowNum, columns, cellStyle);
        } else {
            rowNum = setColumnHead(sheet, rowNum, columns);
        }

        try {
            for (int i = 1; i <= dataList.size(); i++) {
                row = sheet.createRow(rowNum++);
                for (int j = 0; j < columns.size(); j++) {
                    getCellData(columns, dataList, row, i, j);
                }
            }
            File file = new File(filePath);
            if(!file.exists()){
                file.mkdir();
            }
            String fileFullPath = filePath+fileName+EXCEL2007U;
            wb.write(new FileOutputStream(new File(fileFullPath)));
            return fileFullPath;
        } catch (IOException|IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
            log.error(e.getMessage(), e);
            throw new MyException(ResponseCode.EXPORT_EXCEL_FAILED);
        }
    }

    /**
     * dataList中的元素为javaBean类型导出
     * @param filePath
     * @param fileName
     * @param columns
     * @param dataList
     * @param fieldThreshold
     */
    public static String exportExcelWithColor(String filePath, String fileName, List<ColumnVO> columns,
                                              List dataList, Map<String, Double> fieldThreshold) {
        // 声明一个工作薄
        XSSFWorkbook wb = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = wb.createSheet("sheet1");
        //创建标题行
        XSSFRow row = sheet.createRow(0);
        //单元格格式
        XSSFCellStyle style = wb.createCellStyle();
        Font boldFont = wb.createFont();
        boldFont.setBold(true);
        style.setFont(boldFont);

//        创建excel表头
//        XSSFCell cell = null;
//        for (int i = 0; i < columns.size(); i++) {
//            cell = row.createCell(i);
//            cell.setCellType(CellType.STRING);
//            String unit = columns.get(i).getUnit();
//
//            if (StringUtils.hasText(unit)) {
//                cell.setCellValue(columns.get(i).getName() + "(" + unit + ")");
//            } else {
//                cell.setCellValue(columns.get(i).getName());
//            }
//        }

        int rowNum = 0;
        if (needMerge(columns)) {
            XSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);

            List<CellRangeAddress> mergeHeadRegion = getMergeHeadRegion(columns);
            mergeHeadRegion.forEach(sheet::addMergedRegion);
            rowNum = setMergedColumnHead(sheet, rowNum, columns, cellStyle);
        } else {
            rowNum = setColumnHead(sheet, rowNum, columns);
        }

        try {
            for (int i = 1; i <= dataList.size(); i++) {
                row = sheet.createRow(rowNum++);
                for (int j = 0; j < columns.size(); j++) {
                    getCellData(columns, dataList, row, i, j,fieldThreshold,wb);
                }
            }
            File file = new File(filePath);
            if(!file.exists()){
                file.mkdir();
            }
            String fileFullPath = filePath+fileName+EXCEL2007U;
            wb.write(new FileOutputStream(new File(fileFullPath)));
            return fileFullPath;
        } catch (IOException|IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
            log.error(e.getMessage(), e);
            throw new MyException(ResponseCode.EXPORT_EXCEL_FAILED);
        }
    }

    /**
     * dataList中的元素为javaBean类型导出
     * @param filePath
     * @param fileName
     * @param columnsList
     * @param dataListList
     */
    public static String exportMultiSheetExcel(String filePath, String fileName, List<List<ColumnVO>> columnsList,
                                              List<List> dataListList) {
        XSSFWorkbook wb = null;
        try {
            // 声明一个工作薄
             wb = new XSSFWorkbook();

            int k = 0;
            for (List<ColumnVO> columns : columnsList) {
                // 生成一个表格
                XSSFSheet sheet = wb.createSheet("sheet" + k);
                //创建标题行
                XSSFRow row = sheet.createRow(0);
                //单元格格式
                XSSFCellStyle style = wb.createCellStyle();
                Font boldFont = wb.createFont();
                boldFont.setBold(true);
                style.setFont(boldFont);
                //创建excel表头
                XSSFCell cell = null;
                for (int i = 0; i < columns.size(); i++) {
                    cell = row.createCell(i);
                    cell.setCellType(CellType.STRING);
                    String unit = columns.get(i).getUnit();

                    if (StringUtils.hasText(unit)) {
                        cell.setCellValue(columns.get(i).getName() + "(" + unit + ")");
                    } else {
                        cell.setCellValue(columns.get(i).getName());
                    }
                }
                try {
                    for (int i = 1; i <= dataListList.get(k).size(); i++) {
                        row = sheet.createRow(i);
                        for (int j = 0; j < columns.size(); j++) {
                            getCellData(columns, dataListList.get(k), row, i, j);
                        }
                    }
                } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
                    log.error(e.getMessage(), e);
                    throw new MyException(ResponseCode.EXPORT_EXCEL_FAILED);
                }

                k++;
            }
            try {

                File file = new File(filePath);
                if (!file.exists()) {
                    file.mkdir();
                }
                String fileFullPath = filePath + fileName + EXCEL2007U;
                wb.write(new FileOutputStream(new File(fileFullPath)));
                return fileFullPath;
            } catch (IOException e) {
                log.error(e.getMessage(), e);
                throw new MyException(ResponseCode.EXPORT_EXCEL_FAILED);
            }
        }finally {
            if(wb!=null){
                try {
                    wb.close();
                } catch (IOException e) {
                    log.error(e.getMessage(),e);
                }
            }
        }

    }

    /**
     * dataList中的元素为javaBean类型导出
     * @param filePath
     * @param fileName
     * @param columnsList
     * @param dataListList
     */
    public static String exportMultiSheetExcel(String filePath, String fileName, List<List<ColumnVO>> columnsList,
                                               List<List> dataListList,List<String> sheetNames) {
        XSSFWorkbook wb = null;
        try {
            // 声明一个工作薄
            wb = new XSSFWorkbook();

            int k = 0;
            for (List<ColumnVO> columns : columnsList) {
                // 生成一个表格
                XSSFSheet sheet = wb.createSheet(sheetNames.get(k));
                //创建标题行
                XSSFRow row = sheet.createRow(0);
                //单元格格式
                XSSFCellStyle style = wb.createCellStyle();
                Font boldFont = wb.createFont();
                boldFont.setBold(true);
                style.setFont(boldFont);
                //创建excel表头
                XSSFCell cell = null;
                for (int i = 0; i < columns.size(); i++) {
                    cell = row.createCell(i);
                    cell.setCellType(CellType.STRING);
                    String unit = columns.get(i).getUnit();

                    if (StringUtils.hasText(unit)) {
                        cell.setCellValue(columns.get(i).getName() + "(" + unit + ")");
                    } else {
                        cell.setCellValue(columns.get(i).getName());
                    }
                }
                try {
                    for (int i = 1; i <= dataListList.get(k).size(); i++) {
                        row = sheet.createRow(i);
                        for (int j = 0; j < columns.size(); j++) {
                            getCellData(columns, dataListList.get(k), row, i, j);
                        }
                    }
                } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
                    log.error(e.getMessage(), e);
                    throw new MyException(ResponseCode.EXPORT_EXCEL_FAILED);
                }

                k++;
            }
            try {

                File file = new File(filePath);
                if (!file.exists()) {
                    file.mkdir();
                }
                String fileFullPath = filePath + fileName + EXCEL2007U;
                wb.write(new FileOutputStream(new File(fileFullPath)));
                return fileFullPath;
            } catch (IOException e) {
                log.error(e.getMessage(), e);
                throw new MyException(ResponseCode.EXPORT_EXCEL_FAILED);
            }
        }finally {
            if(wb!=null){
                try {
                    wb.close();
                } catch (IOException e) {
                    log.error(e.getMessage(),e);
                }
            }
        }

    }


    private static void getCellData(List<ColumnVO> columns, List dataList, XSSFRow row, int i, int j) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        XSSFCell cell;
        cell = row.createCell(j);
        cell.setCellType(CellType.STRING);
        Class clazz = dataList.get(i - 1).getClass();
        if (!columns.get(j).getIgnore()) {
            Object value;
            if (dataList.get(i - 1) instanceof Map) {
                value = ((HashMap) dataList.get(i - 1)).get(columns.get(j).getKey());
            } else {
                Method method = clazz.getMethod("get" + StringUtils.capitalize(columns.get(j).getKey()));
                value = method.invoke(dataList.get(i - 1));
            }

            if (null != value) {
                cell.setCellValue(value.toString());
            } else {
                cell.setCellValue("");
            }

        }
    }

    /**
     * 判断是否要设置字体颜色
     * @param columns
     * @param dataList
     * @param row
     * @param i
     * @param j
     * @param fieldThreshold
     * @throws NoSuchMethodException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
    private static void getCellData(List<ColumnVO> columns, List dataList, XSSFRow row, int i, int j, Map<String,
            Double> fieldThreshold,XSSFWorkbook wb ) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        XSSFCell cell;
        cell = row.createCell(j);
        cell.setCellType(CellType.STRING);
        Class clazz = dataList.get(i - 1).getClass();

        if (!columns.get(j).getIgnore()) {
            Object value;
            if (dataList.get(i - 1) instanceof Map) {
                value = ((HashMap) dataList.get(i - 1)).get(columns.get(j).getKey());
            } else {
                Method method = clazz.getMethod("get" + StringUtils.capitalize(columns.get(j).getKey()));
                value = method.invoke(dataList.get(i - 1));
            }

            if (null != value) {
                String s = value.toString();
                if(StringUtils.hasText(s) && fieldThreshold.containsKey(columns.get(j).getKey())){
                    double v = Double.parseDouble(s);
                    if( v< fieldThreshold.get(columns.get(j).getKey())){
                        //设置字体颜色
                        XSSFFont font = wb.createFont();
                        font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
                        XSSFCellStyle style = wb.createCellStyle();
                        style.setFont(font);
                        cell.setCellStyle(style);
                    }
                }
                cell.setCellValue(value.toString());
            } else {
                cell.setCellValue("");
            }

        }
    }

    private static int setColumnHead(XSSFSheet sheet, int rowNum, List<ColumnVO> columns) {
        XSSFRow row = sheet.createRow(rowNum++);
        int columnNum = 0;
        for (ColumnVO column : columns) {
            if (column.getIgnore()) {
                continue;
            }
            XSSFCell cell = row.createCell(columnNum++);
            cell.setCellType(CellType.STRING);
            String unit = StringUtils.isEmpty(column.getUnit()) ? "" : String.format("(%s)", column.getUnit());
            cell.setCellValue(column.getName() + unit);
        }
        return rowNum;
    }

    private static int setMergedColumnHead(XSSFSheet sheet, int rowNum, List<ColumnVO> columns, CellStyle cellStyle) {
        XSSFRow row1 = sheet.createRow(rowNum++);
        XSSFRow row2 = sheet.createRow(rowNum++);
        int columnNum = 0;
        for (ColumnVO column : columns) {
            if (column.getIgnore()) {
                continue;
            }

            XSSFCell cell2 = row2.createCell(columnNum);
            cell2.setCellType(CellType.STRING);
            String unit = StringUtils.isEmpty(column.getUnit()) ? "" : String.format("(%s)", column.getUnit());
            String cellValue = column.getName() + unit;
            cell2.setCellValue(cellValue);

            XSSFCell cell1 = row1.createCell(columnNum++);
            cell1.setCellType(CellType.STRING);
            cell1.setCellStyle(cellStyle);
            cell1.setCellValue(StringUtils.isEmpty(column.getMergeNameKey())?cellValue:column.getMergeNameKey());
        }
        return rowNum;
    }

    private static boolean needMerge(List<ColumnVO> columns) {
        for (ColumnVO column : columns) {
            if (column.getMergeName() != null) {
                return true;
            }
        }
        return false;
    }

    private static List<CellRangeAddress> getMergeHeadRegion(List<ColumnVO> columns) {
        List<CellRangeAddress> regionList = new ArrayList<>();
        LinkedHashMap<String, List<ColumnVO>> mergeMap = columns.stream().collect(
                Collectors.groupingBy(
                        column -> StringUtils.isEmpty(column.getMergeName()) ? column.getKey() : column.getMergeName(),
                        LinkedHashMap::new,
                        Collectors.toList()));
        int columnIndex = 0;
        CellRangeAddress cellAddresses;
        for (Map.Entry<String, List<ColumnVO>> entry : mergeMap.entrySet()) {
            List<ColumnVO> columnList = entry.getValue();
            if (columnList.size() > 1) {
                cellAddresses = new CellRangeAddress(0, 0, columnIndex,
                        columnIndex + columnList.size() - 1);
                columnIndex += columnList.size();
            } else {
                cellAddresses = new CellRangeAddress(0, 1, columnIndex, columnIndex);
                columnIndex++;
            }
            regionList.add(cellAddresses);
        }
        return regionList;
    }
}
